using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;
using TeamTime.Classes;
using StillVoiceCommon;

namespace TeamTime
{
    /// <summary>
    /// Summary description for AutoComplete
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    public class AutoComplete : System.Web.Services.WebService
    {

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        [WebMethod]
        public string[] getCompanies(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                    "Select top 50 CompanyName from ViewCompanies Where CompanyName like @prefixText+'%' order by CompanyName",
                    new SqlParameter("prefixText", prefixText));


                while (dr.Read())
                {
                    t.Add(dr[0].ToString());

                }

            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }
        [WebMethod]
        public string[] getProjects(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                    "Select top 20 ProjectName from ViewTN5YProjects Where ProjectName like @prefixText+'%' order by ProjectName",
                    new SqlParameter("prefixText", prefixText));


                while (dr.Read())
                {
                    t.Add(dr[0].ToString());

                }

            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }


        [WebMethod]
        public string[] getTasks(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                    "select top 20 CompanyProjectTask from ViewTasks Where CompanyProjectTask like '%'+@prefixText+'%' order by CompanyProjectTask",
                    new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());

                    }
                }
            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }

        [WebMethod]
        public string[] getInvoiceNumbers(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                     "select distinct top 30 InvoiceNumber from TimeEntry Where InvoiceNumber like @prefixText+'%' order by InvoiceNumber",
                     new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());
                    }
                }
            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }
        [WebMethod]
        public string[] getEnterprises(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                     "Select top 50 CompanyName from ViewEnterprises Where CompanyName like @prefixText+'%' order by CompanyName",
                     new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());

                    }
                }
            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();

        }
        [WebMethod]
        public string[] getPersons(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                      "Select top 50 FullNameAndID from ViewPersons Where FullNameAndID like @prefixText+'%' order by FullNameAndID",
                      new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());

                    }
                }
            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }

        [WebMethod]
        public string[] getConsultants(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                      "Select top 50 FullNameAndID from ViewStaffMembers sm inner join Consultant c on sm.ID = c.staffMemberID " +
                          " Where FullNameAndID like @prefixText+'%' order by FullNameAndID",
                      new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());
                    }
                }
            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }

        [WebMethod]
        public string[] getVenues(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                       "Select distinct top 50  Venue  from Events.Event " +
                           " Where Venue like @prefixText+'%' order by Venue",
                       new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());
                    }
                }
            }
            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }

        [WebMethod]
        public string[] getSupplierServices(string prefixText)
        {
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                     "Select top 50 Description from SupplierService Where Description like @prefixText+'%' order by Description",
                     new SqlParameter("prefixText", prefixText)))
                {
                    while (dr.Read())
                    {
                        t.Add(dr[0].ToString());
                    }
                }
            }

            if (t.Count == 0)
            {
                t.Add("No match found");
            }
            return t.ToArray();
        }

        [WebMethod(EnableSession = true)]
        public string[] getStaffMembers(string prefixText)
        {
            prefixText = prefixText.Trim();
            List<string> t = new List<string>();
            if (prefixText != "No match found")
            {
                if (Session["UserID"] != null && Session["UserID"].ToString() != "")
                {
                    int UserID = (int)Session["UserID"];
                    if (UserID != 0)
                    {
                        #region Build SQL
                        string sql;
                        Security security = new Security(UserID);

                        sql = "Select top 50 FullNameAndID from ViewStaffMembers sm " +
                                       " Where sm.FullNameAndID like @prefixText+'%' ";


                        sql += " order by FullNameAndID ";
                        #endregion Build SQL
                        using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                            sql, new SqlParameter("prefixText", prefixText)))
                        {

                            while (dr.Read())
                            {
                                t.Add(dr[0].ToString());
                            }
                        }
                    }
                }
            }
            if (t.Count == 0) { t.Add("No match found"); }
            return t.ToArray();
        }

    }
}
